﻿using OnlineAuction.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OnlineAuction.Common;

namespace OnlineAuction.DataAccess
{
    public class ItemDA
    {
        public bool AddItems(ItemsE item, AuctionDTO auction)
        {

            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Add item to database
                try
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand("AddItems", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CategoryID", item.CategoryID));
                    command.Parameters.Add(new SqlParameter("@ItemName", item.ItemName));
                    command.Parameters.Add(new SqlParameter("@ItemImage1", item.ItemImage1));
                    command.Parameters.Add(new SqlParameter("@ItemImage2", item.ItemImage2));
                    command.Parameters.Add(new SqlParameter("@ItemImage3", item.ItemImage3));
                    command.Parameters.Add(new SqlParameter("@ItemImage4", item.ItemImage4));
                    command.Parameters.Add(new SqlParameter("@ItemDescription", item.ItemDescription));
                    command.Parameters.Add(new SqlParameter("@AuctionTypeID", auction.AuctionTypeID));
                    command.Parameters.Add(new SqlParameter("@FirstPrice", auction.FirstPrice));
                    command.Parameters.Add(new SqlParameter("@ExpectationPrice", auction.ExpectationPrice));
                    command.Parameters.Add(new SqlParameter("@TimeFinishAuction", auction.TimeFinishAuction));
                    command.Parameters.Add(new SqlParameter("@BidIncrement", auction.BidIncrement));
                    command.Parameters.Add(new SqlParameter("@TimeIncrement", auction.TimeIncrement));
                    command.Parameters.Add(new SqlParameter("@UserName", auction.Seller));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet ItemsSelling(string userName)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Get all items selling
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter("ItemsSelling", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@UserName", userName);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet ItemsDisabled(string userName)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Get all items selling
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter("ItemsDisabled", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@UserName", userName);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet DetailItemSelling(int auctionID)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Get all detail item selling
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter("DetailItemSelling", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@AuctionID", auctionID);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool UpdateItems(ItemsE item)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //update item to database
                try
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand("UpdateItems", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@AuctionID", item.AuctionID));
                    command.Parameters.Add(new SqlParameter("@CategoryID", item.CategoryID));
                    command.Parameters.Add(new SqlParameter("@ItemName", item.ItemName));
                    command.Parameters.Add(new SqlParameter("@ItemImage1", item.ItemImage1));
                    command.Parameters.Add(new SqlParameter("@ItemImage2", item.ItemImage2));
                    command.Parameters.Add(new SqlParameter("@ItemImage3", item.ItemImage3));
                    command.Parameters.Add(new SqlParameter("@ItemImage4", item.ItemImage4));
                    command.Parameters.Add(new SqlParameter("@ItemDescription", item.ItemDescription));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet Search(string itemName, int categoryID)
        {
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter("Search", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@ItemName", itemName);
                    da.SelectCommand.Parameters.AddWithValue("@CategoryID", categoryID);
                    da.Fill(ds, "Auction");
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool DeleteItem(string ItemID)
        {
            SqlConnection conn = new SqlConnection();
            Database db = new Database();
            try
            {
                using (conn = db.GetConnection())
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("DeleteItem", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ItemID", ItemID);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
            }
            catch
            {
                conn.Close();
                return false;
            }
            finally
            {
                conn.Dispose();
            }
        }
    }
}
